#!/usr/bin/env python
# encoding: utf-8
# @Time    : 2019/10/14 16:02
# @Author  : ZRD
# @File    : db.py
import sqlite3, sys, os
from .log import LOGGER


def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d


class Sqlite3cls:
    """
    增删改查操作
    """

    def __init__(self):
        try:
            dbfilepath = os.path.join(os.path.abspath(sys.path[0]), '.taskdb.sqlite')
            self._conn = sqlite3.connect(dbfilepath)
            self._conn.row_factory = dict_factory
            self._cursor = self._conn.cursor()
        except Exception as e:
            print("初始化数据库错误", e)

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self._cursor.close()
        self._conn.close()

    def task_count(self) -> int:
        """ 获取任务总数 """
        res = self._cursor.execute("SELECT COUNT(*) AS count  FROM task")
        return res.fetchone().get('count')

    def task_create(self, values: dict):
        sql = """INSERT INTO "task" ("task_name", "taskid", "workdir", "pre_ommand", "post_ommand") VALUES (?, ?,?, ?, ?) """
        try:
            res = self._cursor.execute(sql, [
                values.get("task_name"),
                values.get("taskid"),
                values.get("workdir"),
                values.get("pre_ommand"),
                values.get("post_ommand"),
            ])
            self._conn.commit()
            return True, res.rowcount
        except Exception as e:
            LOGGER.error(f"插入数据错误{sql} ,{values} , {e}")
            _, v, _ = sys.exc_info()
            return False, str(v)

    def task_get_by_task_name(self, task_name):
        sql = "SELECT * FROM task WHERE `task_name`=? LIMIT 1 "

        try:
            res = self._cursor.execute(sql, [task_name]).fetchone()
            LOGGER.debug(f'{sql}, 返回的数据:{res}')
            if res:
                return 1
            return 0

        except Exception as e:
            LOGGER.error(f"查询数据错误{sql} {task_name} , {e}")
            return 0

    def task_getone(self, ):
        sql = "SELECT * FROM task LIMIT 1 "
        try:
            res = self._cursor.execute(sql, )
            return res.fetchone()
        except Exception as e:
            LOGGER.error(f"查询数据错误{sql}  , {e}")
            return {}

    def task_delete(self, rid: int):
        sql = f'DELETE FROM "task" WHERE "id" = {rid}'
        try:
            res = self._cursor.execute(sql, )
            self._conn.commit()
            msg = f"删除任务完成: {sql}, 影响:{self._conn.total_changes}行"
            LOGGER.debug(msg)

            return self._conn.total_changes
        except Exception as e:
            LOGGER.error(f"删除数据错误{sql} {rid} , {e}")
            return 0
